package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.common.DBConn;
import com.entity.OrderInfo;
import com.entity.PageModel;
import com.util.PublicToolCheckParam;

public class OrderInfoDAO {

	public OrderInfo addAndGetOrderInfo(int customerId) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String orderTime = sdf.format(new Date());
		String sql = "insert into OrderInfo (`customerId`, `status`, `orderTime`) values("
				+ customerId + ",0,'" + orderTime + "')";
		Connection conn = DBConn.getConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {

			pstmt = conn.prepareStatement(sql);

			pstmt.executeUpdate();

			pstmt.close();
			pstmt = conn
					.prepareStatement("select * from OrderInfo where orderId = @@identity");
			rs = pstmt.executeQuery();

			if (rs.next()) {
				OrderInfo od = new OrderInfo();
				od.setOrderId(rs.getInt("orderId"));
				od.setCustomerId(rs.getInt("customerId"));
				od.setStatus(rs.getInt("status"));
				od.setOrderTime(rs.getString("orderTime"));

				return od;
			}

			return null;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {

			DBConn.CloseConn(rs, pstmt, conn);
		}
		return null;
	}

	public void deleteOrder(int orderId) {
		String sql1 = "delete from OrderGoodsInfo where orderId=" + orderId;
		DBConn.ExecuteUD(sql1);
		String sql2 = "delete from OrderInfo where orderId=" + orderId;
		DBConn.ExecuteUD(sql2);
	}
	/**
	 * 鏌ヨ棰勭害琛ㄤ俊鎭�
	 * @param orderId
	 * @return
	 */
	public OrderInfo getOrderInfo(int orderId) {
		String sql = "select * from OrderInfo where orderId=" + orderId;
		ArrayList<Object> list = DBConn.ExecuteQuery(sql, new OrderInfo());

		if (list != null) {
			if (list.size() == 1) {
				return (OrderInfo) list.get(0);
			}
		}
		return null;
	}
	/**
	 * 鏌ヨ灏辫瘖淇℃伅
	 * @param customerId
	 * @return
	 */
	public ArrayList<Object> getOrderInfoByCustomerId(int customerId) {
		String sql = "select * from OrderInfo where customerId=" + customerId
						+" order by orderTime desc";
		return DBConn.ExecuteQuery(sql, new OrderInfo());
	}

	/**
	 * 查询管理员-门诊就诊分页
	 * @param pm
	 * @return
	 */
	public ArrayList<Object> getPageModel(PageModel pm) {
		int everyPageBulletinCount = pm.getEveryPageCount();//
		int currentPage = pm.getCurrentPage();
		if (pm.getSumCount() % everyPageBulletinCount != 0
				&& currentPage == pm.getLastPage()) {
			everyPageBulletinCount = pm.getSumCount() % everyPageBulletinCount;
		}
		String tsql = "SELECT * FROM OrderInfo a ORDER BY a.orderTime DESC"
					+ " LIMIT " + (currentPage - 1) * pm.getPageSize() +","+currentPage * pm.getPageSize();
		return DBConn.ExecuteQuery(tsql, new OrderInfo());
	}

	public int getSumOrderCount() {

		String sql = "select * from OrderInfo";
		ArrayList<Object> list = DBConn.ExecuteQuery(sql, new OrderInfo());

		if (list != null) {
			return list.size();
		}
		return 0;
	}

	// 宸茬‘璁ゆ�绘暟
	public int getqueren() {

		String sql = "select * from OrderInfo where status=1";
		ArrayList<Object> list = DBConn.ExecuteQuery(sql, new OrderInfo());

		if (list != null) {
			return list.size();
		}
		return 0;
	}

	// 宸叉寕鍙锋帓闃熶腑鎬绘暟
	public int getwqueren() {

		String sql = "select * from OrderInfo where status=0";
		ArrayList<Object> list = DBConn.ExecuteQuery(sql, new OrderInfo());

		if (list != null) {
			return list.size();
		}
		return 0;
	}

	// 鍙栨秷娌荤枟鎬绘暟
	public int gettuihuo() {

		String sql = "select * from OrderInfo where status=101";
		ArrayList<Object> list = DBConn.ExecuteQuery(sql, new OrderInfo());

		if (list != null) {
			return list.size();
		}
		return 0;
	}

	public int getSumSearchOrderCount(String key) {

		String sql = "select * from OrderInfo where orderTime like '%" + key
				+ "%'";

		ArrayList<Object> list = DBConn.ExecuteQuery(sql, new OrderInfo());
		if (list != null) {
			return list.size();
		}
		return 0;

	}

	// 宸茬‘璁ゆ�绘暟
	public int getsqueren(String key) {

		String sql = "select * from OrderInfo where status=1 and  orderTime like '%"
				+ key + "%'";
		ArrayList<Object> list = DBConn.ExecuteQuery(sql, new OrderInfo());

		if (list != null) {
			return list.size();
		}
		return 0;
	}

	// 宸叉寕鍙锋帓闃熶腑鎬绘暟
	public int getswqueren(String key) {

		String sql = "select * from OrderInfo where status=0  and  orderTime like '%"
				+ key + "%'";
		ArrayList<Object> list = DBConn.ExecuteQuery(sql, new OrderInfo());

		if (list != null) {
			return list.size();
		}
		return 0;
	}

	// 鍙栨秷娌荤枟鎬绘暟
	public int getstuihuo(String key) {

		String sql = "select * from OrderInfo where status=101 and  orderTime like '%"
				+ key + "%'";
		ArrayList<Object> list = DBConn.ExecuteQuery(sql, new OrderInfo());

		if (list != null) {
			return list.size();
		}
		return 0;
	}

	public ArrayList<Object> searchBulletin(PageModel pm, String key) {

		int everyPageBulletinCount = pm.getEveryPageCount();

		int currentPage = pm.getCurrentPage();

		if (pm.getSumCount() % everyPageBulletinCount != 0
				&& currentPage == pm.getLastPage()) {

			everyPageBulletinCount = pm.getSumCount() % everyPageBulletinCount;

		}
		String sql = "select * from (select  * from (select  * from OrderInfo where orderTime like '%"
				+ key
				+ "%' order by orderId desc limit 0,"
				+ pm.getEveryPageCount()
				* pm.getCurrentPage()
				+ ") as a order by orderId desc limit 0,"
				+ everyPageBulletinCount + ") as b order by orderId desc";
		return DBConn.ExecuteQuery(sql, new OrderInfo());
	}

	// ****************show all order for admin *******************

	// ****************show all order for third *******************
	public ArrayList<Object> getPageModelforthird(PageModel pm, String thirdId, String lsdate) {

		int everyPageBulletinCount = pm.getEveryPageCount();//

		int currentPage = pm.getCurrentPage();

		if (pm.getSumCount() % everyPageBulletinCount != 0
				&& currentPage == pm.getLastPage()) {

			everyPageBulletinCount = pm.getSumCount() % everyPageBulletinCount;

		}
	 String wheresql="";
	 if(lsdate.equals("now"))wheresql=" and to_days(a.orderTime) = to_days(now())  ";
	 if(lsdate.equals("old"))wheresql=" and to_days(a.orderTime) < to_days(now())  ";
		String tSql =  "select * from OrderInfo a"
					+ " left join customerinfo b on a.customerId = b.id"
					+ " LEFT JOIN ordergoodsinfo c on a.orderId = c.orderId"
					+ " LEFT JOIN goodsinfo d on c.goodsId = d.goodsId"
					+ " where d.userid = " + thirdId + wheresql
					+ " order by a.orderTime desc"
					+ " LIMIT " + (currentPage - 1) * pm.getPageSize() +","+currentPage * pm.getPageSize();
		return DBConn.ExecuteQuery(tSql, new OrderInfo());
	}

	public int getSumOrderCountforthird(String thirdId,String lsdate) {

		String wheresql="";
		
		if(lsdate.equals("now"))wheresql=" and to_days(orderTime) = to_days(now()) ";
		if(lsdate.equals("old"))wheresql=" and to_days(orderTime) < to_days(now()) ";
			
		String tSql =  "select * from OrderInfo a"
					+ " left join customerinfo b on a.customerId = b.id"
					+ " LEFT JOIN ordergoodsinfo c on a.orderId = c.orderId"
					+ " LEFT JOIN goodsinfo d on c.goodsId = d.goodsId"
					+ " where d.userid = " + thirdId + wheresql;
		ArrayList<Object> list = DBConn.ExecuteQuery(tSql, new OrderInfo());

		if (list != null && list.size() > 0) {
			return list.size();
		}
		return 0;
	}

	public int getSumSearchOrderCountforthird(String key, String thirdId) {

		String sql = "select * from OrderInfo where orderTime like '%"
				+ key
				+ "%' and  orderid in (select orderid from OrdergoodsInfo where  goodsid in (select goodsid from goodsinfo where userid='"
				+ thirdId + "')) ";

		ArrayList<Object> list = DBConn.ExecuteQuery(sql, new OrderInfo());
		if (list != null) {
			return list.size();
		}
		return 0;

	}

	public ArrayList<Object> searchBulletinforthird(PageModel pm, String key,
			String thirdId) {

		int everyPageBulletinCount = pm.getEveryPageCount();

		int currentPage = pm.getCurrentPage();

		if (pm.getSumCount() % everyPageBulletinCount != 0
				&& currentPage == pm.getLastPage()) {

			everyPageBulletinCount = pm.getSumCount() % everyPageBulletinCount;

		}
		String sql = "select * from (select * from (select  * from OrderInfo where orderTime like '%"
				+ key
				+ "%' and   orderid in (select orderid from OrdergoodsInfo where  goodsid in (select goodsid from goodsinfo where userid='"
				+ thirdId
				+ "'))  order by orderId desc limit 0,"
				+ pm.getEveryPageCount()
				* pm.getCurrentPage()
				+ ") as a order by orderId desc  LIMIT 0,"
				+ everyPageBulletinCount + ") as b order by orderId desc";
		return DBConn.ExecuteQuery(sql, new OrderInfo());
	}

	// ****************show all order for third *******************

	public void updateOrderStatus(int orderId, int status) {

		if (PublicToolCheckParam.checkNullAndEmpty(status)) {
			if (status == 1) {
				status = 0;
			} else if (status == 0) {
				status = 1;
			}
		}

		String sql = "update OrderInfo set status = " + status
				+ " where orderId=" + orderId;
		DBConn.ExecuteUD(sql);
	}
	/**
	 * 查询订单明细
	 * @param parseInt
	 * @return
	 */
	public List<Object> getOrderInfoDetail(int orderId) {
		String tSql =  "select * from OrderInfo a"
				+ " left join customerinfo b on a.customerId = b.id"
				+ " LEFT JOIN ordergoodsinfo c on a.orderId = c.orderId"
				+ " LEFT JOIN goodsinfo d on c.goodsId = d.goodsId"
				+ " where a.orderId = " + orderId;
		System.out.println(tSql);
		return DBConn.ExecuteQuery(tSql, new OrderInfo());
	}

}
